use crate::error::TodoError;
use crate::storage::{InsertTodoItem, Status, Storage, TodoItem, UpdateTodoItem};
use anyhow::Result;
use chrono::{NaiveDate, NaiveDateTime};
use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSqlOutput, ValueRef};
use rusqlite::{params, Connection, Error, Row, ToSql};
use std::{env, fs};

pub struct SqliteStorage {
    conn: Connection,
}

impl SqliteStorage {
    pub fn new() -> Result<Self> {
        // 获取当前二进制文件所在目录
        let current_path = env::current_exe()?;
        let exe_dir = current_path
            // 获取父目录，即二进制文件所在的目录
            .parent()
            // 如果没有父目录，则返回错误
            .ok_or(TodoError::DBCreateError("无法获取当前程序所在的目录"))?;

        // 在当前目录下创建 data 文件夹
        let data_dir = exe_dir.join("data");
        // 创建 data 文件夹（如果不存在）
        fs::create_dir_all(&data_dir)?;

        // 在该目录下创建数据库文件
        let db_path = data_dir.join("todo.db");
        let conn = Connection::open(db_path)?;
        Self::initialize_database(&conn)?;
        Ok(SqliteStorage { conn })
    }

    fn initialize_database(conn: &Connection) -> Result<()> {
        conn.execute(
            r#"CREATE TABLE IF NOT EXISTS todos (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    content TEXT NOT NULL,
                    status INTEGER NOT NULL DEFAULT 0,
                    date DATE NOT NULL,
                    completed_at DATETIME DEFAULT NULL,
                    priority INTEGER NOT NULL DEFAULT 0,
                    created_at DATETIME,
                    priority_updated_at DATETIME,
                    updated_at DATETIME
                );
                CREATE INDEX IF NOT EXISTS idx_todos_date ON todos (date);
                CREATE INDEX IF NOT EXISTS idx_todos_status ON todos (status);"#,
            [],
        )?;
        Ok(())
    }
}

impl Storage for SqliteStorage {
    /// 新增一个新的待办
    fn insert(&self, todo: &InsertTodoItem) -> Result<i32> {
        let mut stmt = self.conn.prepare("INSERT INTO todos(content, date, priority, created_at, priority_updated_at, updated_at) VALUES(?, ?, ?, STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime'), STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime'), STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime'));")?;
        stmt.execute(params![todo.content,TodoDate::from(todo.date),todo.priority])?;
        Ok(self.conn.last_insert_rowid() as i32)
    }

    /// 按条件查询待办列表
    fn select(&self, after: &NaiveDate, status: &Option<Status>) -> Result<Vec<TodoItem>> {
        let query = match status {
            None => "SELECT * FROM todos WHERE date >= ? ORDER BY date DESC, priority DESC, status ASC ,priority_updated_at DESC;",
            Some(_) => "SELECT * FROM todos WHERE date >= ? AND status = ? ORDER BY date DESC, priority DESC, status ASC ,priority_updated_at DESC;",
        };

        let mut stmt = self.conn.prepare(query)?;

        let mut rows = match status {
            None => stmt.query(params![after.format("%Y-%m-%d").to_string()])?,
            Some(s) => {
                let s: u8 = s.into();
                stmt.query(params![after.format("%Y-%m-%d").to_string(),s])?
            }
        };

        let mut todos = Vec::new();
        while let Some(row) = rows.next()? {
            let todo_item = TodoItem::from_row(&row)?;
            todos.push(todo_item);
        }

        Ok(todos)
    }

    /// 复制指定任务并设置date为当天
    fn cp(&self, id: u32) -> Result<u32> {
        let mut stmt = self.conn.prepare("INSERT INTO todos(content, date, priority)
                                SELECT content, DATE('now','localtime'), priority
                                FROM todos WHERE id = ?;")?;
        stmt.execute(params![id])?;

        let id = self.conn.last_insert_rowid() as u32;

        Ok(id)
    }

    /// 根据id查询待办
    fn find_by_id(&self, id: u32) -> Result<Option<TodoItem>> {
        let mut stmt = self.conn.prepare("SELECT * FROM todos WHERE id = ?;")?;

        match stmt.query_row(params![id], |row| {
            Ok(Some(TodoItem::from_row(row).unwrap()))
        }) {
            Ok(todo) => Ok(todo),
            Err(e) => {
                match e {
                    Error::QueryReturnedNoRows => Ok(None),
                    _ => Err(e)?,
                }
            }
        }
    }

    /// 更新待办的date
    fn update_date(&self, id: u32, date: &NaiveDate) -> Result<()> {
        let mut stmt = self.conn.prepare("UPDATE todos SET date = ? WHERE id = ?")?;
        stmt.execute(params![date.format("%Y-%m-%d").to_string(),id])?;
        Ok(())
    }

    fn delete_by_id(&self, id: u32) -> Result<()> {
        let mut stmt = self.conn.prepare("DELETE FROM todos WHERE id = ?")?;
        stmt.execute(params![id])?;
        Ok(())
    }

    // 根据日期删除待办
    fn delete_by_date(&self, date: &NaiveDate) -> Result<()> {
        let mut stmt = self.conn.prepare("DELETE FROM todos WHERE date = ?")?;
        stmt.execute(params![date.format("%Y-%m-%d").to_string()])?;
        Ok(())
    }

    // 更新待办
    fn update(&self, update: &UpdateTodoItem) -> Result<()> {
        match self.find_by_id(update.id)? {
            None => {
                println!("未找到待办");
                Ok(())
            }
            Some(old) => {
                let content = update.content.clone().unwrap_or_else(|| old.content);
                let priority = update.top_priority.map(|p| if p { u8::MAX } else { u8::MIN }).unwrap_or_else(|| old.priority);
                let date = update.date.unwrap_or_else(|| old.date).format("%Y-%m-%d").to_string();

                let mut stmt = self.conn.prepare("UPDATE todos SET content = ?, priority = ?, date = ?, priority_updated_at = STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime') WHERE id = ?")?;
                stmt.execute(params![content,priority,date,update.id])?;
                Ok(())
            }
        }
    }

    /// 更新待办的状态
    fn update_status(&self, id: u32, status: Status, top_priority: Option<bool>) -> Result<()> {
        let sql = match top_priority {
            None => "UPDATE todos SET status = ? WHERE id = ?",
            Some(_) => "UPDATE todos SET status = ?, priority = ? WHERE id = ?",
        };

        let mut stmt = self.conn.prepare(sql)?;

        match top_priority {
            None => {
                stmt.execute(params![u8::from(status).to_string(),id])?;
                Ok(())
            }
            Some(b) => {
                let priority = if b { u8::MAX } else { u8::MIN };
                stmt.execute(params![u8::from(status).to_string(),priority,id])?;
                Ok(())
            }
        }
    }

    /// 清空全部数据
    fn clear(&self) -> Result<()> {
        self.conn.execute("DROP TABLE todos;", params![])?;
        Ok(())
    }
}

impl TodoItem {
    fn from_row(row: &Row) -> Result<Self> {
        Ok(TodoItem {
            id: row.get(0)?,
            content: row.get(1)?,
            status: row.get(2)?,
            date: row.get::<usize, TodoDate<NaiveDate>>(3)?.into(),
            completed_at: row.get::<usize, TodoDate<NaiveDateTime>>(4).ok().map(|c| c.into()),
            priority: row.get(5)?,
            created_at: row.get::<usize, TodoDate<NaiveDateTime>>(6).ok().map(|c| c.into()),
            priority_updated_at: row.get::<usize, TodoDate<NaiveDateTime>>(6).ok().map(|c| c.into()),
            updated_at: row.get::<usize, TodoDate<NaiveDateTime>>(6).ok().map(|c| c.into()),
        })
    }
}

struct TodoDate<T>(T);

impl From<NaiveDate> for TodoDate<NaiveDate> {
    fn from(value: NaiveDate) -> Self {
        Self(value)
    }
}

impl From<TodoDate<NaiveDate>> for NaiveDate {
    fn from(value: TodoDate<NaiveDate>) -> Self {
        value.0
    }
}

impl ToSql for TodoDate<NaiveDate> {
    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
        let date_str = self.0.format("%Y-%m-%d").to_string();
        Ok(ToSqlOutput::Owned(date_str.into()))
    }
}

impl FromSql for TodoDate<NaiveDate> {
    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
        // 将 ValueRef 转换为字符串，然后解析为 NaiveDate
        match value.as_str() {
            Ok(date_str) => NaiveDate::parse_from_str(date_str, "%Y-%m-%d")
                .map(TodoDate)
                .map_err(|_| FromSqlError::InvalidType),
            Err(_) => Err(FromSqlError::InvalidType),
        }
    }
}


impl From<NaiveDateTime> for TodoDate<NaiveDateTime> {
    fn from(value: NaiveDateTime) -> Self {
        Self(value)
    }
}

impl From<TodoDate<NaiveDateTime>> for NaiveDateTime {
    fn from(value: TodoDate<NaiveDateTime>) -> Self {
        value.0
    }
}

impl ToSql for TodoDate<NaiveDateTime> {
    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
        let date_str = self.0.format("%Y-%m-%d %H:%M:%S").to_string();
        Ok(ToSqlOutput::Owned(date_str.into()))
    }
}

impl FromSql for TodoDate<NaiveDateTime> {
    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
        // 将 ValueRef 转换为字符串，然后解析为 NaiveDate
        match value.as_str() {
            Ok(date_str) => NaiveDateTime::parse_from_str(date_str, "%Y-%m-%d %H:%M:%S")
                .map(TodoDate)
                .map_err(|_| FromSqlError::InvalidType),
            Err(_) => Err(FromSqlError::InvalidType),
        }
    }
}


